package com.android.nextBus.DB;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import com.android.nextBus.Data.CredentialsItem;
import com.android.nextBus.JSON.TripsTimesItem;
import com.google.android.maps.GeoPoint;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.format.DateFormat;
import android.util.Log;

public class SQLiteOperationsWrapper {

	/*
	 * Defines a handle to the database helper object. The MainDatabaseHelper
	 * class is defined in a following snippet.
	 */
	private DatabaseHelper mOpenHelper;

	// Defines the database name
	private static final String DBNAME = "transitDb";

	private final Context context;

	// Holds the database object
	private SQLiteDatabase db;

	public SQLiteOperationsWrapper(Context c_t) {
		this.context = c_t;
	}

	/**
	 * Open the DB, or throw a SQLException if we cannot open or create a new
	 * DB.
	 */
	public SQLiteOperationsWrapper open() throws SQLException {
		// instantiate a DatabaseHelper class (see above)
		mOpenHelper = new DatabaseHelper(context);

		// the SQLiteOpenHelper class (a parent of DatabaseHelper)
		// has a "getWritableDatabase" method that returns an
		// object of type SQLiteDatabase that represents an open
		// connection to the database we've opened (or created).
		db = mOpenHelper.getWritableDatabase();
		//db.enableWriteAheadLogging();

		return this;
	}

	/**
	 * Close the DB
	 */
	public void close() {
		mOpenHelper.close();
	}
	
	
	/**
	 * Deletes the selected stop id from the favorites table
	 * @param stopID item to be removed from the table
	 * @return int value representing the number of rows affected, should be 1
	 */
	public int deleteFavorite(int stopID) {
		
		// selection used for the query command
		String whereArgs[] = {"" + stopID};
		
		return db.delete(SQLiteTablesAndColumns.FAVORITES_TABLE, SQLiteTablesAndColumns.STOP_ID_COLUMN + "=?", whereArgs);
	}


	/**
	 * Returns an int representing the date in the form: yyyymmdd ex. 20120705
	 * for July 5, 2012
	 * @return int value representing the date
	 */
	public int getDate() {
		Calendar c = Calendar.getInstance(); 
		int year = c.get(Calendar.YEAR); 
		int month = c.get(Calendar.MONTH);
		int day = c.get(Calendar.DAY_OF_MONTH);
		int date = year * 10000 + month * 100 + day;
		
		return date;
	}
	
//	public int getCalendarDay() {
//		Date d = new Date();
//		String day = (String) DateFormat.format("E", d.getTime());
//		String hDay = (String) DateFormat.format("yyyymmdd", d.getTime());
//		
//		if(isHoliday(Integer.parseInt(hDay))) {
//			return 3;
//		} else if(day == "Sun") {
//			return 2;
//		} else if(day == "Sat") {
//			return 1;
//		} else {
//			return 0;
//		}
//		
//	}
	
	
	/**
	 * Insert a stop row into the stops table
	 * @param stopName String
	 * @param stopDesc String
	 * @param stopLat double
	 * @param stopLon double
	 * @param stopID int
	 * @return the row id, or -1 on failure
	 */
	public long insertStopRow(String stopName,String stopDesc,double stopLat,double stopLon,int stopID) {
		
		ContentValues vals = new ContentValues();

		int date = getDate();
		
		vals.put(SQLiteTablesAndColumns.STOP_ID_COLUMN, stopID);
		vals.put(SQLiteTablesAndColumns.STOP_DESC_COLUMN, stopDesc);
		vals.put(SQLiteTablesAndColumns.STOP_NAME_COLUMN, stopName);
		vals.put(SQLiteTablesAndColumns.STOP_LAT_COLUMN, stopLat);
		vals.put(SQLiteTablesAndColumns.STOP_LON_COLUMN, stopLon);
		vals.put(SQLiteTablesAndColumns.ACCESSED_DATE_COLUMN, date);
		vals.put(SQLiteTablesAndColumns.UPDATED_DATE_COLUMN, date);
		
		return db.insert(SQLiteTablesAndColumns.STOPS_TABLE, null, vals);
	}
	
		
	/**
	 * @param pswrd
	 * @param name
	 * @param email
	 * @param country
	 * @param address
	 * @param zipcode
	 * @param busPassNumber 
	 * @return
	 */
	public long insertCredentials(String pswrd, String name, String email, String country, String address, int zipcode, String busPassNumber) {
		
		db.delete(SQLiteTablesAndColumns.CREDENTIALS_TABLE, "1", null);
		
		ContentValues vals = new ContentValues();
		vals.put(SQLiteTablesAndColumns.PASSWORD_COLUMN, pswrd);
		vals.put(SQLiteTablesAndColumns.NAME_COLUMN, name);
		vals.put(SQLiteTablesAndColumns.EMAIL_COLUMN, email);
		vals.put(SQLiteTablesAndColumns.COUNTRY_COLUMN, country);
		vals.put(SQLiteTablesAndColumns.ADDRESS_COLUMN, address);
		vals.put(SQLiteTablesAndColumns.ZIP_CODE_COLUMN, zipcode);
		vals.put(SQLiteTablesAndColumns.BUS_PASS_COLUMN, busPassNumber);
		vals.put(SQLiteTablesAndColumns.FIRST_TIME_FLAG_COLUMN, 0);
		
		return db.insert(SQLiteTablesAndColumns.CREDENTIALS_TABLE, null, vals);
	}
	
	
	/**
	 * Insert a stop time row
	 * @param stopID int
	 * @param tripID int
	 * @param departureTime string
	 * @return the row id, or -1 on failure
	 */
	public long insertStopTimeRow(int stopID, int tripID, int departureTime) {
		ContentValues vals = new ContentValues();
		int date = getDate();
		
		vals.put(SQLiteTablesAndColumns.STOP_ID_COLUMN, stopID);
		vals.put(SQLiteTablesAndColumns.TRIP_ID_COLUMN, tripID);
		vals.put(SQLiteTablesAndColumns.DEPARTURE_TIME_COLUMN, departureTime);
		vals.put(SQLiteTablesAndColumns.ACCESSED_DATE_COLUMN, date);
		vals.put(SQLiteTablesAndColumns.UPDATED_DATE_COLUMN, date);
		
		return db.insert(SQLiteTablesAndColumns.STOP_TIMES_TABLE, null, vals);
	}
	
	
	/**
	 * Insert favorite stop into favorites_table
	 * @param stopID stop id of the favorite stop
	 * @return the row id, or -1 on failure
	 */
	public long insertFavoriteRow(int stopID) {
		ContentValues vals = new ContentValues();
		int date = getDate();
		
		vals.put(SQLiteTablesAndColumns.STOP_ID_COLUMN, stopID);
		vals.put(SQLiteTablesAndColumns.UPDATED_DATE_COLUMN, date);
		vals.put(SQLiteTablesAndColumns.ACCESSED_DATE_COLUMN, date);
		
		return db.insert(SQLiteTablesAndColumns.FAVORITES_TABLE, null, vals);
	}
	
	
	/**
	 * Insert row into routes_stops_table
	 * @param routeID the route_id for this row
	 * @param stopID the stop_id for this row
	 * @return the row id, or -1 on failure
	 */
	public long insertRoutesStopsRow(int routeID, int stopID) {
		ContentValues vals = new ContentValues();
		int date = getDate();
		
		vals.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, routeID);
		vals.put(SQLiteTablesAndColumns.STOP_ID_COLUMN, stopID);
		vals.put(SQLiteTablesAndColumns.UPDATED_DATE_COLUMN, date);
		vals.put(SQLiteTablesAndColumns.ACCESSED_DATE_COLUMN, date);
		
		return db.insert(SQLiteTablesAndColumns.ROUTES_STOPS_TABLE, null, vals);
	}
	
	
	/**
	 * Insert shape row
	 * @param shapeID int
	 * @param shapePtSeq int
	 * @param shapePtLat int
	 * @param shapePtLon int
	 * @return the row id, or -1 on failure
	 */
	public void insertShapeRow(MapItem aMapItem) {
		ContentValues vals = new ContentValues();
		int date = getDate();
		int shapeID = aMapItem.getShapeID();
		
		for(int i = 0; i < aMapItem.getShapePoints().size(); i++) {
			int lat = aMapItem.getShapePoints().get(i).getLatitudeE6();
			int lon = aMapItem.getShapePoints().get(i).getLongitudeE6();
					
			vals.put(SQLiteTablesAndColumns.SHAPE_ID_COLUMN, shapeID);
			vals.put(SQLiteTablesAndColumns.SHAPE_POINT_SEQUENCE_COLUMN, i+1);
			vals.put(SQLiteTablesAndColumns.SHAPE_POINT_LAT_COLUMN, lat);
			vals.put(SQLiteTablesAndColumns.SHAPE_POINT_LON_COLUMN, lon);
			vals.put(SQLiteTablesAndColumns.ACCESSED_DATE_COLUMN, date);
			vals.put(SQLiteTablesAndColumns.UPDATED_DATE_COLUMN, date);
			
			db.insert(SQLiteTablesAndColumns.SHAPES_TABLE, null, vals);
		}
	}
	
	
	/**
	 * Insert trip row
	 * @param tripID int
	 * @param routeID int
	 * @param tripHeadsign string
	 * @param shapeID int
	 * @return the row id, or -1 on failure
	 */
	public long insertTripRow(int tripID, int routeID, String tripHeadsign, int shapeID) {
		ContentValues vals = new ContentValues();
		int date = getDate();
		
		vals.put(SQLiteTablesAndColumns.TRIP_ID_COLUMN, tripID);
		vals.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, routeID);
		vals.put(SQLiteTablesAndColumns.TRIP_HEADSIGN_COLUMN, tripHeadsign);
		vals.put(SQLiteTablesAndColumns.SHAPE_ID_COLUMN, shapeID);
		vals.put(SQLiteTablesAndColumns.ACCESSED_DATE_COLUMN, date);
		vals.put(SQLiteTablesAndColumns.UPDATED_DATE_COLUMN, date);
		
		return db.insert(SQLiteTablesAndColumns.TRIPS_TABLE, null, vals);
	}
	
	
	/**
	 * Insert route row
	 * @param routeID int
	 * @param routeShortName String
	 * @param routeLongName String
	 * @return the row id, or -1 on failure
	 */
	public long insertRouteRow(int routeID, String routeShortName, String routeLongName) {
		ContentValues vals = new ContentValues();
		int date = getDate();
		
		vals.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, routeID);
		vals.put(SQLiteTablesAndColumns.ROUTE_SHORT_NAME_COLUMN, routeShortName);
		vals.put(SQLiteTablesAndColumns.ROUTE_LONG_NAME_COLUMN, routeLongName);
		vals.put(SQLiteTablesAndColumns.ACCESSED_DATE_COLUMN, date);
		vals.put(SQLiteTablesAndColumns.UPDATED_DATE_COLUMN, date);
		
		return db.insert(SQLiteTablesAndColumns.ROUTES_TABLE, null, vals);
	}
	
	
	/**
	 * Inserts a row into the "ROUTES_STOPS" table
	 * @param rID route id for the row
	 * @param sID stop id for the row
	 */
	public void insertRouteStop(int rID, int sID) {
		ContentValues vals = new ContentValues();
		int date = getDate();
		
		vals.put(SQLiteTablesAndColumns.STOP_ID_COLUMN, sID);
		vals.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, rID);
		vals.put(SQLiteTablesAndColumns.ACCESSED_DATE_COLUMN, date);
		vals.put(SQLiteTablesAndColumns.UPDATED_DATE_COLUMN, date);
		
		db.insert(SQLiteTablesAndColumns.ROUTES_STOPS_TABLE, null, vals);
	}
	
	
	public CredentialsItem getCredentials() {
		Cursor cursor = db.rawQuery("select * from credentials", null);
		
		if(!cursor.moveToFirst()) {
			cursor.close();
			return null;
		}
		
		int nameIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.NAME_COLUMN);
		int emailIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.EMAIL_COLUMN);
		int addressIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.ADDRESS_COLUMN);
		int countryIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.COUNTRY_COLUMN);
		int passwordIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.PASSWORD_COLUMN);
		int zipIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.ZIP_CODE_COLUMN);
		int busPassIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.BUS_PASS_COLUMN);
		
		CredentialsItem credentials = new CredentialsItem(cursor.getString(nameIndex), 
				cursor.getString(emailIndex), cursor.getString(countryIndex), cursor.getString(addressIndex), 
				cursor.getInt(zipIndex), cursor.getString(passwordIndex), cursor.getString(busPassIndex));
		
		cursor.close();
		
		return credentials;
		
	}
	
	
	/**
	 * Checks if passed date is a holiday
	 * @param date integer date in the form of yyyymmdd, i.e. 20120903 to test 
	 * @return true or false if the date is a holiday
	 */
	public boolean isHoliday(int date) {
		
		// selection used for the query command
		// String selectionArgs[] = {"" + date};
		
		Cursor cursor = db.query(SQLiteTablesAndColumns.HOLIDAY_TABLE, 
				null, null, null, null, null, null);
		
		if(!cursor.moveToFirst()) {
			cursor.close();
			return false;
		}
		
		// get the number of rows in the cursor
		int count = cursor.getCount();
				
		// loop through the cursor and build the RouteItems array
		for(int i = 0 ; i < count ; i ++) {
			cursor.moveToPosition(i);		
			int holidayIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.HOLIDAY_DATE_COLUMN);
		
			if (cursor.getInt(holidayIndex) == date) {
				cursor.close();
				return true;
			}
			
		}
		
		cursor.close();
		return false;
	}
	
	
	/**
	 * Gets the calendarDayCode by checking whether it is a holiday or which
	 * calendar it is.
	 * @param day integer value between 1 and 7, where sunday = 1, and Saturday = 7
	 * @param date date to get the calendarDayCode
	 * @return integer value for the calendar day code used in searches, 0 = weekday
	 * 1 = saturday, 2 = sunday, 3 = holiday
	 */
	public int getCalendarDayCode(int day, int date) {
		
		Boolean isHoliday = false;
		
		Cursor cursor = db.query(SQLiteTablesAndColumns.HOLIDAY_TABLE, 
				null, null, null, null, null, null);
		
		if (cursor.moveToFirst()) {

			// get the number of rows in the cursor
			int count = cursor.getCount();

			// loop through the cursor and build the RouteItems array
			for (int i = 0; i < count; i++) {
				cursor.moveToPosition(i);
				int holidayIndex = cursor
						.getColumnIndex(SQLiteTablesAndColumns.HOLIDAY_DATE_COLUMN);

				if (cursor.getInt(holidayIndex) == date) {
					cursor.close();
					isHoliday = true;
				}

			}
		}
		
		cursor.close();
		
		
		if(isHoliday) {
			return 3;
		} else if (day == 1) {
			return 2;
		} else if (day == 7) {
			return 1;
		} else {
			return 0;
		}
	}
	
	
	/**
	 * Determines whether there are any times in DB for a given date and day code
	 * @param date Date to check
	 * @param day its calendarDayCode 
	 * @param stopID the stop id to check
	 * @return whether there are any times in the DB for this calendar day.
	 */
	public boolean hasTimesOnDate(int day, int stopID) {
		
		// query for the stop times
		Cursor cursor = db.rawQuery("SELECT * FROM "
				+ SQLiteTablesAndColumns.STOP_TIMES_TABLE + " NATURAL JOIN "
				+ SQLiteTablesAndColumns.TRIPS_TABLE + " WHERE "
				+ SQLiteTablesAndColumns.STOP_ID_COLUMN + " = " + stopID
				+ " and "
				+ SQLiteTablesAndColumns.TRIP_ID_COLUMN + " LIKE '%"
				+ day + "_'",
				null);

		// if we got nothing from the query return false
		if (!cursor.moveToFirst()) {
			cursor.close();
			return false;
		} else {
			cursor.close();
			return true;
		}

	}
	
	
	/**
	 * Returns an ArrayList of RouteItems for all the Routes held in the SQLite
	 * Database
	 * @return an ArrayList of RouteItems containing all the routes available to
	 * search
	 */
	public ArrayList<RouteItem> getAllRouteItems(){
		// query to get all routes within the app
		Cursor cursor = db.query(SQLiteTablesAndColumns.ROUTES_TABLE, null, 
				null, null, null, null, null);
		
		//if we got nothing from the query return null
		if(!cursor.moveToFirst()){
			cursor.close();
			return null;
		}
		
		// initialize the RouteItems array to be returned
		ArrayList<RouteItem> routeItems = new ArrayList<RouteItem>();
		
		// get the number of rows in the cursor
		int count = cursor.getCount();
		
		// loop through the cursor and build the RouteItems array
		for(int i = 0 ; i < count ; i ++){
			cursor.moveToPosition(i);
			
			int routeIDColumnIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.ROUTE_ID_COLUMN);
			int routeShortNameColumnIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.ROUTE_SHORT_NAME_COLUMN);
			int routeLongNameColumnIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.ROUTE_LONG_NAME_COLUMN);

			routeItems.add(new RouteItem(cursor.getInt(routeIDColumnIndex),
							   cursor.getString(routeShortNameColumnIndex),
							   cursor.getString(routeLongNameColumnIndex)));
		}
				
		//close the cursor
		cursor.close();

		// return the RouteItems array
		return routeItems;
		
	}
	
	
	/**
	 * Gets the stop times and associated date from the database
	 * @param stopID id of the stop to get times for
	 * @param time the start time to look in db for
	 * @param date the date to look in db for, used for testing whether the day is a holiday
	 * @param day the integer value for the day, 1 = Sunday, 2 = Monday, ...
	 * , 7 = Saturday.
	 * @return an ArrayList of TripsTimesItems which contain the information about
	 * a trip time
	 */
	public ArrayList<TripsTimesItem> getStopTimes(int stopID, String time, int day) {
		
		
		// query for the stop times
		Cursor cursor = db.rawQuery("SELECT * FROM "
				+ SQLiteTablesAndColumns.STOP_TIMES_TABLE + " NATURAL JOIN "
				+ SQLiteTablesAndColumns.TRIPS_TABLE + " WHERE "
				+ SQLiteTablesAndColumns.STOP_ID_COLUMN + " = " + stopID
				+ " and " + SQLiteTablesAndColumns.DEPARTURE_TIME_COLUMN
				+ " > " + time + " and "
				+ SQLiteTablesAndColumns.TRIP_ID_COLUMN + " LIKE '%"
				+ day + "_'" + " ORDER BY "
				+ SQLiteTablesAndColumns.DEPARTURE_TIME_COLUMN + " LIMIT 6",
				null);

		// if we got nothing from the query return null
		if (!cursor.moveToFirst()) {
			cursor.close();
			return null;
		}

		// initialize TripTimesItem for return value
		ArrayList<TripsTimesItem> tTimes = new ArrayList<TripsTimesItem>();

		// get number of rows in cursor
		int count = cursor.getCount();

		// loop through cursor add each row to the TripsTimesItem array
		for (int i = 0; i < count; i++) {
			cursor.moveToPosition(i);

			int thIndex = cursor
					.getColumnIndex(SQLiteTablesAndColumns.TRIP_HEADSIGN_COLUMN);
			int shIDIndex = cursor
					.getColumnIndex(SQLiteTablesAndColumns.SHAPE_ID_COLUMN);
			int trIDIndex = cursor
					.getColumnIndex(SQLiteTablesAndColumns.TRIP_ID_COLUMN);
			int dtIndex = cursor
					.getColumnIndex(SQLiteTablesAndColumns.DEPARTURE_TIME_COLUMN);
			int rIndex = cursor
					.getColumnIndex(SQLiteTablesAndColumns.ROUTE_ID_COLUMN);

			tTimes.add(new TripsTimesItem(cursor.getString(thIndex), cursor
					.getInt(shIDIndex), cursor.getInt(trIDIndex), cursor
					.getInt(shIDIndex), cursor.getInt(dtIndex), cursor
					.getInt(rIndex)));

		}

		// close cursor
		cursor.close();

		return tTimes;
	}
	
	
	/**
	 * Gets the stops for a specified route
	 * @param routeID id for the route who's stops you wish to find
	 * @return ArrayList<StopItem> containing a list of Stops for this route
	 */
	public ArrayList<StopItem> getStops(int routeID) {
		
		// query for the stops
		Cursor cursor = db.rawQuery("SELECT * FROM " + 
				SQLiteTablesAndColumns.ROUTES_STOPS_TABLE + " natural join " + 
				SQLiteTablesAndColumns.STOPS_TABLE + " WHERE " + 
				SQLiteTablesAndColumns.ROUTE_ID_COLUMN + " = " + routeID, null);
		
		// Initialize StopItem array
		ArrayList<StopItem> stops = new ArrayList<StopItem>();
		
		// return null if the cursor is empty
		if(!cursor.moveToFirst()){
			cursor.close();
			return null;
		}
		
		// get the number of rows in the cursor
		int count = cursor.getCount();
		
		// loop through cursor and add items to the StopItem array
		for(int i = 0; i < count; i++) {
			cursor.moveToPosition(i);
			
			int stopDescIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_DESC_COLUMN);
			int stopIDIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_ID_COLUMN);
			int stopLatIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_LAT_COLUMN);
			int stopLonIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_LON_COLUMN);
			int stopNameIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_NAME_COLUMN);		
			
			StopItem aStop = new StopItem(cursor.getString(stopNameIndex), 
					cursor.getString(stopDescIndex), 
					cursor.getInt(stopLonIndex), 
					cursor.getInt(stopLatIndex), 
					cursor.getInt(stopIDIndex));
			
			stops.add(aStop);
		}
		
		// close cursor
		cursor.close();	
		
		// return the stops for the passed routeID
		return stops;
	}
	
	
	
	public ArrayList<StopItem> getFavorites() {
		
		// query for the stops
		Cursor cursor = db.rawQuery("SELECT favorites.stop_id, stop_name, stop_desc, stop_lat, stop_lon from " +
				"stops join favorites where favorites.stop_id = stops.stop_id", null);
		
		//SELECT favorites.stop_id, stop_name, stop_desc, stop_lat, stop_lon FROM stops join favorites where favorites.stop_id = stops.stop_id;
//		Cursor cursor = db.rawQuery("SELECT * FROM " +
//				SQLiteTablesAndColumns.STOPS_TABLE, null);
				
		// Initialize StopItem array
		ArrayList<StopItem> stops = new ArrayList<StopItem>();
				
		// return null if the cursor is empty
		if(!cursor.moveToFirst()){
			cursor.close();
			return null;
		}
				
		// get the number of rows in the cursor
		int count = cursor.getCount();
				
		// loop through cursor and add items to the StopItem array
		for(int i = 0; i < count; i++) {
			cursor.moveToPosition(i);
					
			int stopDescIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_DESC_COLUMN);
			int stopIDIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_ID_COLUMN);
			int stopLatIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_LAT_COLUMN);
			int stopLonIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_LON_COLUMN);
			int stopNameIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.STOP_NAME_COLUMN);		
					
			StopItem aStop = new StopItem(cursor.getString(stopNameIndex), 
					cursor.getString(stopDescIndex), 
					cursor.getInt(stopLonIndex), 
					cursor.getInt(stopLatIndex), 
					cursor.getInt(stopIDIndex));
					
			stops.add(aStop);
		}
				
		// close cursor
		cursor.close();	
				
		// return the stops for the passed routeID
		return stops;
		
	}

	
	/**
	 * Returns an ArrayList of MapItems that are used to draw the path of a bus
	 * trip on a map.  
	 * @param shapeID the id of the shape to be returned
	 * @return ArrayList of MapItems with all the points to draw the path of a 
	 * bus trip on a map
	 */
	public MapItem getMapPoints(int shapeID) {

		// selection used for the query command
		String selectionArgs[] = {""+shapeID};
		
		// query for the shapeID
		Cursor cursor = db.query(SQLiteTablesAndColumns.SHAPES_TABLE , 
					     null, 
					     SQLiteTablesAndColumns.SHAPE_ID_COLUMN + "=?", 
					     selectionArgs, null, null, SQLiteTablesAndColumns.SHAPE_POINT_SEQUENCE_COLUMN);
		
		//if we got nothing from the query return null
		if(!cursor.moveToFirst()){
			cursor.close();
			return null;
		}
				
		// initialize the GeoPoints array to be used to build a MapItem
		ArrayList<GeoPoint> geoPts = new ArrayList<GeoPoint>();
		
		// the number of rows in the cursor
		int count = cursor.getCount();
		
		// loop through cursor and create geoPoints array.
		for(int i = 0; i < count; i++) {
			cursor.moveToPosition(i);
			
			int shapeLatIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.SHAPE_POINT_LAT_COLUMN);
			int shapeLonIndex = cursor.getColumnIndex(SQLiteTablesAndColumns.SHAPE_POINT_LON_COLUMN);
			
			GeoPoint aPoint = new GeoPoint(cursor.getInt(shapeLatIndex), 
					cursor.getInt(shapeLonIndex));
			
			geoPts.add(aPoint);
		}
		
		// close the cursor
		cursor.close();
		
		// return a MapItem used to draw a shape
		return new MapItem(shapeID, geoPts);
	}
	
	

	/**
	 * Determines whether the app has run once previously
	 * @return 0 if it has run before, and 1 if it has not run before.
	 */
	public int isFirstTime() {

		Cursor cursor = db.query(SQLiteTablesAndColumns.CREDENTIALS_TABLE, null, null, null, null, null, null);
		
		
		if (!cursor.moveToFirst()) {
			// content values to be inserted into the table
			ContentValues vals = new ContentValues();
			
			// add flag to show that the app has now been opened
			vals.put(SQLiteTablesAndColumns.FIRST_TIME_FLAG_COLUMN, 0);
			
			// insert values into the table
			db.insert(SQLiteTablesAndColumns.CREDENTIALS_TABLE, null, vals);
			
			// close cursor
			cursor.close();
			
			// return true indicating the app was not previously opened
			return 1;
			
		} else {
			// close cursor
			cursor.close();
			// return false indicating the app was previously opened
			return 0;
		}
		
	}


	public boolean isFavorite(int stopID) {

		Cursor cursor = db.rawQuery("SELECT * FROM favorites where stop_id = " + stopID, null);
		
		if(!cursor.moveToFirst()) {
			cursor.close();
			return false;
		} else {
			cursor.close();
			return true;
		}
		
	}

	// define an extension of the SQLiteOpenHelper to handle the
	// creation and upgrade of a table
	protected static final class DatabaseHelper extends SQLiteOpenHelper {

		/*
		 * * Instantiates an open helper for the provider's SQLite data
		 * repository * Do not do database creation and upgrade here.
		 */
		public DatabaseHelper(Context context) {
			super(context, DBNAME, null, 1);
		}

		// called by the parent class when a DB doesn't exist
		public void onCreate(SQLiteDatabase db) {
			// Creates the all the tables
			createAllTables(db);
			ContentValues values = new ContentValues();
			values.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, 67);
			values.put(SQLiteTablesAndColumns.ROUTE_LONG_NAME_COLUMN, "W Minnehaha - Wabasha - Smith - Signal Hills");
			values.put(SQLiteTablesAndColumns.ROUTE_SHORT_NAME_COLUMN, 67);
			db.insert(SQLiteTablesAndColumns.ROUTES_TABLE, null, values);
			
			values = new ContentValues();
			values.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, 133);
			values.put(SQLiteTablesAndColumns.ROUTE_LONG_NAME_COLUMN, "Ltd Stop - Bloomington Av - Chicago Av - Mpls");
			values.put(SQLiteTablesAndColumns.ROUTE_SHORT_NAME_COLUMN, 133);
			db.insert(SQLiteTablesAndColumns.ROUTES_TABLE, null, values);
			
			values = new ContentValues();
			values.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, 134);
			values.put(SQLiteTablesAndColumns.ROUTE_LONG_NAME_COLUMN, "Ltd Stop - Cleveland Av - Cretin Av - Mpls");
			values.put(SQLiteTablesAndColumns.ROUTE_SHORT_NAME_COLUMN, 134);
			db.insert(SQLiteTablesAndColumns.ROUTES_TABLE, null, values);
			
			values = new ContentValues();
			values.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, 141);
			values.put(SQLiteTablesAndColumns.ROUTE_LONG_NAME_COLUMN, "Ltd Stop - New Brighton - Johnson St - Mpls");
			values.put(SQLiteTablesAndColumns.ROUTE_SHORT_NAME_COLUMN, 141);
			db.insert(SQLiteTablesAndColumns.ROUTES_TABLE, null, values);
			
			values.put(SQLiteTablesAndColumns.ROUTE_ID_COLUMN, 780);
			values.put(SQLiteTablesAndColumns.ROUTE_LONG_NAME_COLUMN, "Maple Grove - Express - Shepherd of the Grove P&R");
			values.put(SQLiteTablesAndColumns.ROUTE_SHORT_NAME_COLUMN, 780);
			db.insert(SQLiteTablesAndColumns.ROUTES_TABLE, null, values);
			
			//values = new ContentValues();
			//values.put(SQLiteTablesAndColumns.HOLIDAY_DATE_COLUMN, 20120903);
			//db.insert(SQLiteTablesAndColumns.HOLIDAY_TABLE, null, values);
			
		}

		// called by the parent when a DB needs to be upgraded
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// remove the old version and create a new one.
			// If we were really upgrading we'd try to move data over
			db.execSQL("DROP TABLE IF EXISTS " + DBNAME);
			onCreate(db);
		}

	}

	// this method creats all the tables defined in the
	// SafetyPlanContentProviderContract class
	private static void createAllTables(SQLiteDatabase db) {

		for (int j = 0; j < SQLiteTablesAndColumns.MASTER_TABLE_LIST.size(); j++) {
			// call a method to build an SQL create string for a table
			String createString = buildCreateStringForTable(j);
			db.execSQL(createString);
		}
	}

	// this function builds an SQL CREATE string for a table from
	// SafetyPlanContentProviderContract TABLE_NAMES_ARRAY array.
	private static String buildCreateStringForTable(int tableIndex) {
		String result = "CREATE TABLE IF NOT EXISTS ";

		// add table name
		result = result
				+ SQLiteTablesAndColumns.MASTER_TABLE_LIST.get(tableIndex)
						.getTableName();

		result = result + " (";

		// add primary key column

		result = result + " _ID INTEGER PRIMARY KEY, ";

		// add each text column for the table
		int numberOfColumns = SQLiteTablesAndColumns.MASTER_TABLE_LIST.get(
				tableIndex).getTableTextColumnNames().length;
		for (int k = 0; k < numberOfColumns; k++) {

			result = result
					+ SQLiteTablesAndColumns.MASTER_TABLE_LIST.get(tableIndex)
							.getTableTextColumnNames()[k];
			result = result + " TEXT,";

		}

		// add each integer column for the table
		numberOfColumns = SQLiteTablesAndColumns.MASTER_TABLE_LIST.get(
				tableIndex).getTableIntegerColumnNamesArray().length;
		for (int k = 0; k < numberOfColumns; k++) {

			result = result
					+ SQLiteTablesAndColumns.MASTER_TABLE_LIST.get(tableIndex)
							.getTableIntegerColumnNamesArray()[k];
			result = result + " INTEGER,";

		}

		// set up each foreign key column for the table
		numberOfColumns = SQLiteTablesAndColumns.MASTER_TABLE_LIST.get(
				tableIndex).getTableFkColumnNamesArray().length;
		for (int k = 0; k < numberOfColumns; k++) {

			result = result + " FOREIGN KEY (";
			result = result
					+ SQLiteTablesAndColumns.MASTER_TABLE_LIST.get(tableIndex)
							.getTableFkColumnNamesArray()[k];
			result = result + ") REFERENCES ";
			result = result
					+ SQLiteTablesAndColumns.MASTER_TABLE_LIST.get(tableIndex)
							.getTableFkReferencesTableArray()[k];
			result = result + " (_ID),";

		}

		// remove final comma from string
		result = (String) result.subSequence(0, result.length() - 1);

		result = result + ");";

		return result;
	}

}
